﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Data;
using System.Collections;

namespace teacher.Util
{
    public class DbHelper
    {
        public static readonly string ConnectionString = ConfigurationManager.AppSettings["conn"];

        public static SqlConnection getConnection()
        {
            return new SqlConnection(ConnectionString);
        }

        public static int ExecuteNonQuery(String sqlText)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                int value = cmd.ExecuteNonQuery();
                return value;
            }
        }
        public static SqlDataReader ExecuteReader(String sqlText, SqlConnection conn)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                SqlDataReader reader = cmd.ExecuteReader();
                return reader;
            }
            catch (Exception e)
            {

                conn.Close();
                throw e;
                // return null;
            }
        }


        public static DataSet ExecuteDataSet(String sqlText)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = cmd;
                    da.Fill(ds);
                    return ds;
                }
            }
        }

        public static DataTable ExecuteDataTable(String sqlText)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    return dt;
                }
            }
        }

        public static DataTable ExecuteDataTable(String table, String column, String primaryKey, String condition, String order, int pageNumber, int pageSize)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = string.Format(@"select top {0} {1} from {2} where 1=1 {3} and {4} not in(select top {5} {4} from {2} where 1=1 {3} order by {6}) order by {6} ",
                    pageSize, column, table, condition, primaryKey, (pageNumber - 1) * pageSize, order);
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    return dt;
                }
            }
        }

        public static int ExecuteDataCount(String table, String primaryKey, String condition)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = string.Format(@"select count({0}) from {1} where 1=1 {2}", primaryKey, table, condition);
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                if (reader.IsDBNull(0))
                    return 0;
                else
                    return reader.GetInt32(0);
            }
        }


        public static int getSingleInt(String sqlText)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                if (reader.IsDBNull(0))
                    return 0;
                else
                    return reader.GetInt32(0);
            }
            catch (Exception e)
            {
                conn.Close();
                return -1;
            }
            finally { conn.Close(); }
        }

        public static Hashtable ExecuteDataProc(string userId, string schoolid, int pageSize, int pageNumber)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            Hashtable ht = new Hashtable();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "[p_get_grow]";
                cmd.CommandType = CommandType.StoredProcedure;

                IDbDataParameter parme1 = cmd.CreateParameter();
                parme1.DbType = DbType.String;
                parme1.Direction = ParameterDirection.Input;
                parme1.ParameterName = "userid";
                parme1.Value = userId;

                IDbDataParameter parme2 = cmd.CreateParameter();
                parme2.DbType = DbType.String;
                parme2.Direction = ParameterDirection.Input;
                parme2.ParameterName = "schoolid";
                parme2.Value = schoolid;

                IDbDataParameter parme3 = cmd.CreateParameter();
                parme3.DbType = DbType.Int32;
                parme3.Direction = ParameterDirection.Input;
                parme3.ParameterName = "pageSize";
                parme3.Value = pageSize;

                IDbDataParameter parme4 = cmd.CreateParameter();
                parme4.DbType = DbType.Int32;
                parme4.Direction = ParameterDirection.Input;
                parme4.ParameterName = "pageNumber";
                parme4.Value = pageNumber;

                IDbDataParameter parme5 = cmd.CreateParameter();
                parme5.DbType = DbType.Int32;
                parme5.Direction = ParameterDirection.Output;
                parme5.ParameterName = "outCount";

                cmd.Parameters.Add(parme1);
                cmd.Parameters.Add(parme2);
                cmd.Parameters.Add(parme3);
                cmd.Parameters.Add(parme4);
                cmd.Parameters.Add(parme5);
                IDataReader reader = cmd.ExecuteReader();
                dt.Load(reader, LoadOption.Upsert);

                ht["list"] = dt;
                ht["count"] = Convert.ToInt32(parme5.Value);
                cmd.Dispose();
                reader.Close();
                reader.Dispose();
            }
            return ht;
        }

        /// <summary>
        /// 存储过程，分页插叙成长档案
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="schoolid"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNumber"></param>
        /// <param name="jzServerAddress">家长服务端地址</param>
        /// <param name="teacherServerAddress">教师服务端地址</param>
        /// <returns></returns>
        public static Hashtable ExecuteDataProc2(string userId, string schoolid, int pageSize, int pageNumber, string jzServerAddress, string teacherServerAddress)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            Hashtable ht = new Hashtable();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "[p_get_grow]";
                cmd.CommandType = CommandType.StoredProcedure;

                IDbDataParameter parme1 = cmd.CreateParameter();
                parme1.DbType = DbType.String;
                parme1.Direction = ParameterDirection.Input;
                parme1.ParameterName = "userid";
                parme1.Value = userId;

                IDbDataParameter parme2 = cmd.CreateParameter();
                parme2.DbType = DbType.String;
                parme2.Direction = ParameterDirection.Input;
                parme2.ParameterName = "schoolid";
                parme2.Value = schoolid;

                IDbDataParameter parme3 = cmd.CreateParameter();
                parme3.DbType = DbType.Int32;
                parme3.Direction = ParameterDirection.Input;
                parme3.ParameterName = "pageSize";
                parme3.Value = pageSize;

                IDbDataParameter parme4 = cmd.CreateParameter();
                parme4.DbType = DbType.Int32;
                parme4.Direction = ParameterDirection.Input;
                parme4.ParameterName = "pageNumber";
                parme4.Value = pageNumber;

                IDbDataParameter parme5 = cmd.CreateParameter();
                parme5.DbType = DbType.Int32;
                parme5.Direction = ParameterDirection.Output;
                parme5.ParameterName = "outCount";

                cmd.Parameters.Add(parme1);
                cmd.Parameters.Add(parme2);
                cmd.Parameters.Add(parme3);
                cmd.Parameters.Add(parme4);
                cmd.Parameters.Add(parme5);
                IDataReader reader = cmd.ExecuteReader();
                dt.Load(reader, LoadOption.Upsert);

                //增加数据类型列
                dt.Columns.Add(new DataColumn("dType"));//区分是图片文本，还是视频数据
                dt.Columns.Add(new DataColumn("isShowDel"));//是否显示删除按钮
                //查询视频
                if (dt != null && dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        var item = dt.Rows[i];
                        var Id = Convert.ToString(item["Id"]);
                        var uerid2 = Convert.ToString(item["uerid"]);
                        string videoURL = item["videoURL"] is System.DBNull ? "" : Convert.ToString(item["videoURL"]);//视频地址
                        string videoImgURL = item["videoImgURL"] is System.DBNull ? "" : Convert.ToString(item["videoImgURL"]);//视频封面图片
                        string videoURL_TRUE = string.Empty;//全路径视频地址 
                        string videoImgURL_TRUE = string.Empty;//全路径视频封面图片地址 
                        byte dType = 1;//1：没有小视频，2：有小视频
                        if (!string.IsNullOrEmpty(videoURL) && !string.IsNullOrWhiteSpace(videoURL))
                        {
                            if (videoURL.IndexOf("tea") != -1)
                            {
                                //老师发布的小视频
                                videoURL_TRUE = teacherServerAddress + videoURL;
                                videoImgURL_TRUE = teacherServerAddress + videoImgURL;
                            }
                            else
                            {
                                //家长发布的小视频
                                videoURL_TRUE = jzServerAddress + videoURL;
                                videoImgURL_TRUE = jzServerAddress + videoImgURL;
                            }
                            dType = 2;
                        }
                        else
                        {
                            dType = 1;
                        }
                        if (userId.Equals(uerid2))
                        {
                            item["isShowDel"] = 1;//显示删除按钮
                        }
                        else
                        {
                            item["isShowDel"] = 0;
                        }
                        //修改dt列值
                        item["videoURL"] = videoURL_TRUE;
                        item["videoImgURL"] = videoImgURL_TRUE;
                        item["dType"] = dType;
                    }
                }

                ht["list"] = dt;
                ht["count"] = Convert.ToInt32(parme5.Value);
                cmd.Dispose();
                reader.Close();
                reader.Dispose();
            }
            return ht;
        }
    }
}